import mysql.connector  # 导入mysql.connector库
from mysql.connector import Error


class MySQLName:
    def __init__(self, host, password, user):
        self.connection = None
        self.__host = host  # MySQL IP地址
        self.__password = password  # MySQL密码
        self.__user = user  # 用户名
        self.current_database = None  # 当前操作的数据库
        self.cursor = None  # 游标

    def connect(self):
        """连接到 MySQL"""
        try:
            self.connection = mysql.connector.connect(
                host=self.__host,
                user=self.__user,
                password=self.__password,
                port=3306
            )
            self.cursor = self.connection.cursor()
        except Error as e:
            print(f"连接 MySQL 失败: {e}")
            raise

    def show_databases(self):
        """显示所有数据库"""
        try:
            self.cursor.execute("SHOW DATABASES;")
            databases = self.cursor.fetchall()
            return [db[0] for db in databases]  # 提取数据库名
        except Error as e:
            print(f"查询数据库列表失败: {e}")
            raise

    def show_tables(self):
        """显示当前数据库的表信息"""
        if not self.current_database:
            print("请先选择一个数据库。")
            return []
        try:
            self.cursor.execute(f"SHOW TABLES FROM {self.current_database};")
            tables = self.cursor.fetchall()
            return [table[0] for table in tables]  # 提取表名
        except Error as e:
            print(f"查询表列表失败: {e}")
            raise

    def show_data(self, table_name):
        """显示指定表的数据"""
        if not self.current_database or not table_name:
            return "请选择数据库:"
        try:
            query = f"SELECT * FROM {self.current_database}.{table_name};"
            self.cursor.execute(query)
            return self.cursor.fetchall()
        except Error as e:
            print(f"查询表数据失败: {e}")
            raise

    def choose_database(self, database):
        """选择数据库"""
        sql = f"USE {database}"
        try:
            self.cursor.execute(sql)
            self.current_database = database
        except Error as e:
            print(f"选择数据库失败: {e}")
            raise

    def execute_query(self, query):
        """执行给定的 SQL 查询并返回结果"""
        try:
            self.cursor.execute(query)
            query = query.strip().lower()
            if query.startswith("show"):
                return [data[0] for data in self.cursor.fetchall()]  # 返回列表形式
            if query.startswith("select") or query.startswith("desc"):
                return self.cursor.fetchall()
            else:
                self.connection.commit()  # 提交事务
                # return "操作成功"
        except Error as e:
            print(f"执行失败: {e}")
            raise

    def get_columns(self, table_name):
        """获取数据表的表头"""
        if not self.current_database or not table_name:
            return "请选择数据库:"
        try:
            query = f"DESC {self.current_database}.{table_name};"
            self.cursor.execute(query)
            columns = []
            for data in self.cursor.fetchall():
                columns.append(data[0])
            return columns
        except Error as e:
            print(f"查询表数据失败: {e}")
            raise

    def close(self):
        """关闭数据库连接和游标"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
